﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using UniMana.Data;

namespace UniMana.Data
{
    public static class Training_SubjectData
    {
        /// <summary>
        /// (thi tot nghiep) danh sach mon hoc theo chuong trinh dao tao
        /// </summary>
        /// <param name="tSubject"></param>
        /// <param name="tProgramSchedule"></param>
        /// <param name="programKey"></param>
        /// <returns></returns>
        public static IQueryable GetListSubjectByProgram(this Table<Training_Subject> tSubject,
                                                        Table<Training_TrainingProgram_Schedule> tProgramSchedule,
                                                        int programKey)
        {
            var l = from subject in tSubject
                    from programSchedule in tProgramSchedule

                    where subject.Training_SubjectKey == programSchedule.Training_SubjectKey
                    where programSchedule.Training_TrainingProgramKey == programKey
                    orderby programSchedule.Catalogue_TermKey descending
                    select new
                    {
                        subject.Training_SubjectKey,
                        subject.Name,
                        subject.ID,
                        programSchedule.Catalogue_TermKey,
                        programSchedule.Training_TrainingProgramKey
                    };
            return l;
        }

        #region [Hữu]
        /// <summary>
        /// Lấy danh sách môn học  có trong chương trình đào tạo
        /// </summary>
        /// <param name="nSubject"></param>
        /// <param name="nTrnDetail"></param>
        /// <param name="niTrainingProgramKey"></param>
        /// <returns></returns>
        public static IQueryable GetListInTrainingProgram(this Table<Training_Subject> nSubject,
                                                             Table<Training_TrainingProgram_Detail> nTrnDetail,
                                                             int niTrainingProgramKey)
        {
            var result = from sbj in nSubject
                         where (from trnDtl in nTrnDetail
                                where trnDtl.Training_TrainingProgramKey == niTrainingProgramKey
                                select trnDtl.Training_SubjectKey).Contains(sbj.Training_SubjectKey)
                         select sbj;
            return result;
        }
        #endregion

        #region [An]
        /// <summary>
        /// Lấy tổng số tín chỉ của môn
        /// </summary>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nTrainingProgramDetail"></param>
        /// <param name="m_iSubject"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iTraingingType"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static double GetTotalCreditsOfSubject(this Table<Training_Subject> nTraining_Subject,
            int m_iTraining_SubjectKey)
        {
            var total = from s in nTraining_Subject
                        where s.Training_SubjectKey == m_iTraining_SubjectKey
                        select s.TotalCredits;
            return total.FirstOrDefault().Value;
        }


        /// <summary>
        /// Lấy danh sách môn mở có trong chương trình đào tạo
        /// </summary>
        /// <param name="nTraining_Subject"></param>
        /// <param name="nTraining_TrainingProgram_Detail"></param>
        /// <param name="nTraining_Subject_Open"></param>
        /// <param name="nTraining_TrainingProgram"></param>
        /// <returns></returns>

        public static IQueryable GetSubjectOpenInTrainingProgram(this Table<Training_Subject> nTraining_Subject,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_Subject_Open> nTraining_Subject_Open,
            Table<Training_TrainingProgram> nTraining_TrainingProgram)
        {
            var values = from t in nTraining_TrainingProgram_Detail
                         join t0 in nTraining_Subject_Open on new { Training_SubjectKey = t.Training_SubjectKey } equals new { Training_SubjectKey = Convert.ToInt32(t0.Training_SubjectKey) }
                         join t1 in nTraining_TrainingProgram on t.Training_TrainingProgramKey equals t1.Training_TrainingProgramKey
                         join t2 in nTraining_Subject on t.Training_SubjectKey equals t2.Training_SubjectKey
                         where
                           t0.Closed == false
                         select new
                         {
                             t.Training_SubjectKey,
                             t2.ID,
                             t2.Name,
                             t2.TotalCredits,
                             t1.Training_TrainingProgramKey,
                             ProgramID = t1.ID,
                             ProgramName = t1.Name
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Lấy khóa, mã, tên, số tín chỉ của môn học thuộc khoa mà giảng viên chưa đăng ký
        /// </summary>
        /// <param name="nSubject"></param>
        /// <returns></returns>
        
        public static IQueryable GetKeyIDNameTotalDepartmentPersonNotRegist(this Table<Training_Subject> nTraining_Subject,
            Table<Personnel_Personnel_Subject> nPersonnel_Personnel_Subject,
            int m_iDepartmentKey,
            string m_strPersonKey)
        {
            var subjectDepartment = from t in nTraining_Subject
                                    where t.Catalogue_DepartmentKey == m_iDepartmentKey ||
                                    t.Catalogue_DepartmentKey == 5
                                    select new
                                    {
                                        t.Catalogue_DepartmentKey,
                                        t.Training_SubjectKey,
                                        t.ID,
                                        t.Name,
                                        t.TotalCredits
                                    };
            var personSubject = from t in nPersonnel_Personnel_Subject
                                where t.Personnel_PersonnelKey.ToString() == m_strPersonKey
                                select new
                                {
                                    t.Training_SubjectKey
                                };
            var result = from t in subjectDepartment
                         where
                          !(from t1 in personSubject
                            select new { t1.Training_SubjectKey }).Contains(new { t.Training_SubjectKey })
                         select new
                         {
                             t.Catalogue_DepartmentKey,
                             t.Training_SubjectKey,
                             t.ID,
                             t.Name,
                             t.TotalCredits
                         };
            return result;
        }


        /// <summary>
        /// Lấy khóa, mã, tên, số tín chỉ của môn học thuộc khoa của giảng viên
        /// </summary>
        /// <param name="nSubject"></param>
        /// <returns></returns>

        public static IQueryable GetKeyIDNameTotalDepartmentPersonRegist(this Table<Training_Subject> nTraining_Subject,
            Table<Personnel_Personnel_Subject> nPersonnel_Personnel_Subject,
            string m_strPersonKey)
        {
            var subjectDepartment = from t in nTraining_Subject
                                    select new
                                    {
                                        t.Catalogue_DepartmentKey,
                                        t.Training_SubjectKey,
                                        t.ID,
                                        t.Name,
                                        t.TotalCredits
                                    };
            var personSubject = from t in nPersonnel_Personnel_Subject
                                where t.Personnel_PersonnelKey.ToString() == m_strPersonKey
                                select new
                                {
                                    t.Training_SubjectKey
                                };
            var result = from t in subjectDepartment
                         where
                          (from t1 in personSubject
                           select new { t1.Training_SubjectKey }).Contains(new { t.Training_SubjectKey })
                         select new
                         {
                             t.Catalogue_DepartmentKey,
                             t.Training_SubjectKey,
                             t.ID,
                             t.Name,
                             t.TotalCredits
                         };
            return result;
        }
        #endregion

        #region [Hòa]
        /// Lấy danh sách các môn theo lớp
        /// <param name="nTraining_Subject"></param>
        /// <param name="_iDepartmentKey">Mã lớp</param>
        /// <returns></returns>
        public static IQueryable GetListByClassKey(
            this Table<Training_Subject> n_Training_Subject,
            Table<Training_TrainingProgram_Detail> n_TrainingProgram_Detail,
            Table<Training_Class> n_Training_Class,
            int _iClassKey,
            int _iSubjectKey,
            int _isamekey = -1)
        {
            if (_isamekey > 0)
            {
                var Iquery = from c in n_Training_Class
                             join pr_dt in n_TrainingProgram_Detail
                                        on c.Training_TrainingProgramKey
                                        equals pr_dt.Training_TrainingProgramKey
                             join sj in n_Training_Subject
                                        on pr_dt.Training_SubjectKey
                                        equals sj.Training_SubjectKey
                             where c.Training_ClassKey == _iClassKey &&
                                   sj.Subject_SameKey == _isamekey
                             select sj;
                return Iquery;
            }
            else
            {
                var Iquery = from c in n_Training_Class
                             join pr_dt in n_TrainingProgram_Detail
                                        on c.Training_TrainingProgramKey
                                        equals pr_dt.Training_TrainingProgramKey
                             join sj in n_Training_Subject
                                        on pr_dt.Training_SubjectKey
                                        equals sj.Training_SubjectKey
                             where c.Training_ClassKey == _iClassKey &&
                                   sj.Training_SubjectKey == _iSubjectKey
                             select sj;
                return Iquery;
            }
        }

        /// Lấy danh sách các môn theo khoa
        /// <param name="nTraining_Subject"></param>
        /// <param name="_iDepartmentKey">Mã khoa</param>
        /// <returns></returns>
        public static IQueryable GetListByDepartmentKey(
            this Table<Training_Subject> nTraining_Subject,
            int _iDepartmentKey)
        {
            var subjectList = nTraining_Subject.Where(
                p => p.Catalogue_DepartmentKey == _iDepartmentKey);
            return subjectList;
        }
        /// <summary>
        /// Lấy môn trùng theo subject key
        /// </summary>
        /// <param name="nTraining_Subject"></param>
        /// <param name="_iSubject_SameKey"></param>
        /// <returns></returns>
        public static IQueryable GetListSameBySubjectKey(
           this Table<Training_Subject> nTraining_Subject,
           int _iSubjectKey)
        {
            var subject = nTraining_Subject.Where(
                p => p.Training_SubjectKey == _iSubjectKey);
            var subjectsame = from sj in nTraining_Subject
                              join sj_key in subject
                                          on sj.Subject_SameKey
                                          equals sj_key.Subject_SameKey
                              select sj;
            return subjectsame;
        }

        /// Lấy các môn trùng nhau theo samekey
        /// <param name="nTraining_Subject"></param>
        /// <param name="_iSubject_SameKey"></param>
        /// <returns></returns>
        public static IQueryable GetBySubjectSameKey(
            this Table<Training_Subject> nTraining_Subject,
            int _iSubject_SameKey)
        {
            var subject = nTraining_Subject.Where(
                p => p.Subject_SameKey == _iSubject_SameKey);
            return subject;
        }

        /// Lấy danh sách môn theo mã
        /// <param name="nTraining_Subject"></param>
        /// <param name="_iSubjectKey"></param>
        /// <returns></returns>
        public static IQueryable GetBySubjectKey(
            this Table<Training_Subject> nTraining_Subject,
            int _iSubjectKey)
        {
            var subject = nTraining_Subject.Where(
                p => p.Training_SubjectKey == _iSubjectKey);
            return subject;
        }

        public static IQueryable GetListOpenByRef(
    this Table<Training_Subject> nSubject,
    Table<Training_Subject_Open> nSubject_Open,
    int _iCourseKey,
    int _iLevelkey,
    int _iTrainingType,
    int _iMajor,
    bool _bClose = false)
        {
            var subjectList = from subject in nSubject
                              join subjectOpen in nSubject_Open on subject.Training_SubjectKey equals subjectOpen.Training_SubjectKey
                              where
                                subjectOpen.Catalogue_MajorKey == _iMajor
                                && subjectOpen.Catalogue_LevelKey == _iLevelkey
                                && subjectOpen.Catalogue_TrainingTypeKey == _iTrainingType
                                && subjectOpen.Closed == _bClose
                              select new
                              {
                                  subject.Training_SubjectKey,
                                  subject.ID,
                                  subject.Name,
                              };
            return subjectList;

        }
        #endregion
    }
}
